import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

/**
 * 
 * @author leonardo
 *
 * 1) Replace any comma in the spread sheet
 * 2) Replace ' for \' eg.: O'Brien
 * 3) Generate CSV, separator comma, char limit empty
 * 4) Remove header
 * 
 ### Fields 
  		 #								
0 - Project
1 - Batch
2 - ETSA Noti
3 - LifeSupport
4 - NMI
5 - GeoArea
6 - CustomerName
7 - PhoneNumber
8 - StreetNumer
9 - StreetNumber(name)
10 -Suburb
11 -Postcode
12 -State
13 -AddressObject1
14 -AddressObject2
15 -AccessInstructions
16 -Hazard
17 -MeterLocation
18 -SpecialIsntructions
19 -KeyId

20 -Meter1			
21 -Meter2 
22 -Meter3
23 -Meter4
24 -Meter5
 
25 -UBD 
26 -Routenumber 
27 -LongV 
28 -LatV 
29 -Post_StreetNo 
30 -Post_StreetName 
31 -Post_StreetType 
32 -PostalSuburb
33 -PostalPostCode
34 -PostalState
35 -Post_AddrObj

 * 
 */

public class LoadCsv {	
	
	static Connection conn = null;

	public static void main(String[] args) {
		LoadCsv csv = new LoadCsv();
		String sql="";
		try {

			FileInputStream fstream = new FileInputStream("/home/leonardo/Desktop/2009_AMRS_MeterChangeMasterList_AdditionalDetails_20090506.csv");

			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			String strLine;
			
			Statement stm = conn.createStatement();
			conn.setAutoCommit(false);
			
			int totLines = 0;
			int totMeters = 0;
			System.out.println("$$$ Init: " + new Date());
			while ((strLine = br.readLine()) != null) {
				totLines ++;
				//Project,Batch,ETSA Noti #,Life Support,NMI,Geo Area,Customer Name,Phone Number,Street Numer,Street Number,Suburb,Postcode,State,Address Object 1,Address Object 2,Access Instructions,Hazard #1,Meter Location,Special Isntructions,Key Id,Meter #1,Meter #2,Meter #3,Meter #4,Meter #5,UBD,Route number,LongV,LatV,Post_StreetNo,Post_StreetName,Post_StreetType,Postal Suburb,Postal Post Code,Postal State,Post_AddrObj
				//System.out.println(strLine);
				sql = " insert into etsaCustomer (";
				
				String fields = " project, batch, etsaNotification, lifeSupport, nmi, geoArea, customerName, phone, streetNumber, streetName, suburb, postcode, state, AddressObject1, AddressObject2, accessInstructions, hazard, meterLocation, specialInformation, keyId, ubd, route, longV, latV , postalStreetNumber, postalStreetName, postalStreetType, postalSuburb, postalPostcode, postalState, postalAddrObj";
				
				sql = sql + fields + " ) values ( "; 
				String [] ff = strLine.split(",");
				
				for (String str : ff) {
					//System.out.println(str);
				}
				if (ff[3].equals("yes")){
					ff[3] = "true";
				}else{ 
					ff[3] = "false";
				}
				String values = "'" + ff[0] + "', " +	"'" + ff[1] + "', " + "'" + ff[2] + "', " + "" + ff[3] + ", " + "'" + ff[4] + "', " + "'" + 
					ff[5] + "', " + "'" + ff[6] + "', " + "'" + ff[7] + "', " + "'" + ff[8] + "', " + "'" + ff[9] + "', " + "'" + ff[10] + "', "
					+ "'" + ff[11] + "', " + "'" + ff[12] + "', " + "'" + ff[13] + "', " + "'" + ff[14] + "', " + "'" + ff[15] + "', " + "'" + ff[16] + "', "
					+ "'" + ff[17] + "', " + "'" + ff[18] + "', " + "'" + ff[19] + "', "  
					+ "'" + ff[25] + "', " + "'" + ff[26] + "', " + "'" + ff[27] + "', "
					+ "'" + ff[28] + "', " + "'" + ff[29] + "', " + "'" + ff[30] + "', " + "'" + ff[31] + "', " + "'" + ff[32] + "', " + "'" + ff[33] + "', "
					+ "'" + ff[34] + "', " + "'" + ff[35] + "'";
									
				sql = sql + values + " )";
				//System.out.println(sql);
				stm.execute(sql,  Statement.RETURN_GENERATED_KEYS);
				int autoIncKeyFromApi = -1;
			    ResultSet rs = stm.getGeneratedKeys();

				if (rs.next()) {
			        autoIncKeyFromApi = rs.getInt(1);
			        //System.out.println(ff[6] + " key is " + autoIncKeyFromApi);
			        if (autoIncKeyFromApi == -1){
			        	throw new Exception("Error!!! Line " + totLines + " does not have meters!!");
			        }else{
			        	//insert meter...
			        	    	
			        for(int ii = 20; ii <= 24; ii++){
			        	String sqlM = "insert into etsaMeter (" +
			        			"stokItemNumber, meterNumber, register, typeEntry, etsaCustomerId, statusId) values(";
			        	if (!ff[ii].equals("")){
			        		String [] mm = ff[ii].split("/");
			        		
			        		sqlM = sqlM + "'" + mm[0] + "', ";
			        		
			        		String[] mnumber = mm[1].split("#");
			        		sqlM = sqlM + "'" + mnumber[1] + "', ";
			        		
			        		sqlM = sqlM + "'" + mm[2] + "', ";
			        		sqlM = sqlM + "'csv', ";
			        		sqlM = sqlM + "" + autoIncKeyFromApi + ", ";
			        		sqlM = sqlM + " 10 )" ;
			        		//System.out.println("sqlMeter: " + sqlM);
			        		totMeters ++;
			        		stm.execute(sqlM);
			        	}
			        }	

			        	
			        	
			        }
			    }
				if (totLines == 8000){
					break;
				}
								
			}//end loop
			conn.commit();			
			System.out.println("$$$ End: " + new Date());
			System.out.println("### Total Lines: " + totLines);
			System.out.println("### Total Meters: " + totMeters);
			in.close();
			stm.close();
			conn.close();
		} catch (Exception e) {
			System.out.println(sql + "\n");
			System.err.println("Error: " + e.getMessage());
			try {
				conn.rollback();
				conn.close();
			} catch (SQLException e1) {				
				e1.printStackTrace() ;
			}
		}

	}

	public LoadCsv() {
		try {
			System.out.println("### connecting...");
			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/AuditApplication", "root", "");
			conn = connection;
			System.out.println("### Connected!!!");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
